/**** Creating database with appropriate settings begins ****/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ExamSys_DBSchema')
DROP DATABASE [ExamSys_DBSchema]
GO

CREATE DATABASE [ExamSys_DBSchema]  ON (NAME = N'ExamSys_DBSchema_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ExamSys_DBSchema_Data.MDF' , SIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N'ExamSys_DBSchema_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ExamSys_DBSchema_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

EXECUTE sp_dboption N'ExamSys_DBSchema', N'autoclose', N'true'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'bulkcopy', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'trunc. log', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'torn page detection', N'true'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'read only', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'dbo use', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'single', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'autoshrink', N'true'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'ANSI null default', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'recursive triggers', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'ANSI nulls', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'concat null yields null', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'cursor close on commit', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'default to local cursor', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'quoted identifier', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'ANSI warnings', N'false'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'auto create statistics', N'true'

EXECUTE sp_dboption N'ExamSys_DBSchema', N'auto update statistics', N'true'

USE [ExamSys_DBSchema]
GO

/**** Create Nova User of it does not exist begins ****/
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'exam')
BEGIN
	DECLARE @logindb NVARCHAR(132), @loginlang NVARCHAR(132) 
	SELECT @logindb = N'master', @loginlang = N'us_english'
	IF @logindb IS NULL OR NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = @logindb)
	SELECT @logindb = N'master'
	IF @loginlang IS NULL OR ( NOT EXISTS (SELECT * FROM master.dbo.syslanguages WHERE name = @loginlang) AND @loginlang <> N'us_english')
	SELECT @loginlang = @@language
	EXECUTE sp_addlogin N'exam', 'exam123', @logindb, @loginlang
END
GO

EXECUTE sp_addsrvrolemember N'exam', sysadmin
GO

EXECUTE sp_addsrvrolemember N'exam', securityadmin
GO

EXECUTE sp_addsrvrolemember N'exam', serveradmin
GO

EXECUTE sp_addsrvrolemember N'exam', setupadmin
GO

EXECUTE sp_addsrvrolemember N'exam', processadmin
GO

EXECUTE sp_addsrvrolemember N'exam', diskadmin
GO

EXECUTE sp_addsrvrolemember N'exam', dbcreator
GO

EXECUTE sp_addsrvrolemember N'exam', bulkadmin
GO

IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'exam' AND uid < 16382)
EXECUTE sp_grantdbaccess N'exam', N'exam'
GO

EXECUTE sp_addrolemember N'db_owner', N'exam'
GO

USE [ExamSys_DBSchema]
GO
/**** Create Nova User of it does not exist ends ****/
/**** Creating database with appropriate settings ends ****/